package com.scalar.chiptrack.finance.proformainvoice.dao;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.finance.proformainvoice.ProformaInvoiceLineItem;
import com.scalar.chiptrack.finance.proformainvoice.forms.ProformaInvoiceForm;
import com.scalar.chiptrack.users.User;
import com.scalar.chiptrack.users.dao.UserDAO;
import com.scalar.chiptrack.commons.Address;
import com.scalar.chiptrack.commons.ClientInfo;
import com.scalar.chiptrack.sales.dao.SalesOrderDAO;
import com.scalar.chiptrack.sales.dao.CustomerDAO;
import com.scalar.chiptrack.sales.SalesOrder;
import com.scalar.chiptrack.sales.Orders;
import com.scalar.chiptrack.marketing.dao.MarketingDAO;
import com.scalar.chiptrack.operations.process.dao.FinishedGoodsDAO;
import com.scalar.chiptrack.operations.process.FinishedGoods;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.Calendar;
import java.sql.*;

import org.apache.struts.util.LabelValueBean;

/**
 * User: prasad
 * Date: Nov 6, 2007
 * Time: 12:07:03 PM
 */
public class ProformaInvoiceDAO
{

    public static ArrayList loadApprovedSalesOrderList( DBTransaction dbTransaction ) throws ScalarException
    {
        Connection conn             = null;
        PreparedStatement pstmt     = null;
        ResultSet rset              = null;
        ArrayList salesOrderlist    = new ArrayList();

        try 
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT DISTINCT SO.SALESORDER_ID, SO.SALESORDER_NO FROM SALES_ORDER SO, CUSTOMER_OPEN_ORDER_LINEITEM COOL WHERE COOL.SALESORDER_ID = SO.SALESORDER_ID AND ( SO_IS_SAMPLE = 0 OR SO_IS_SAMPLE = 1 ) AND COOL_STATUS = 'APPROVED' AND SO.SALESORDER_ID NOT IN ( SELECT DISTINCT SALESORDER_ID FROM CUSTOMER_SHIPPING_TABLE  WHERE ( CST_STATUS = 'ISSUED' OR CST_STATUS = 'SHIPPED' ) ) ORDER BY SO.SALESORDER_NO" );
            rset = pstmt.executeQuery();

            while( rset.next() )
            {
                salesOrderlist.add( new LabelValueBean( rset.getString ( "SALESORDER_NO" ), rset.getString ( "SALESORDER_ID" ) ) );
            }
        }
        catch( Exception e )
        {
            throw new ScalarException( "Could not retrieve approved sales order numbers from database", e );
        }
        finally
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return salesOrderlist;
    }

    public static ArrayList loadLineItemDetails( DBTransaction dbTransaction, int salesOrderId, int invoiceId ) throws ScalarException
    {

        Connection conn                 = null;
        PreparedStatement pstmt         = null;
        //CallableStatement cstmt       = null;
        ResultSet rset                  = null;
        ProformaInvoiceLineItem lineItem= null;
        ArrayList lineItemList          = new ArrayList();

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            pstmt = conn.prepareStatement( "{ CALL SP_LOAD_PROFOMA_INVOICE_SALES_ORDER_LINE_ITEMS( ?, ? ) }" );
            pstmt.setInt( 1, salesOrderId );
            pstmt.setInt( 2, invoiceId );

            rset 	= pstmt.executeQuery();

            while( rset.next() )
            {
                lineItem = new ProformaInvoiceLineItem();

                lineItem.setProInvoiceLineItemId( rset.getInt( "PILI_ID" ) );           /* PROFORMA INVOICE LINE ITEM ID */
                if( lineItem.getProInvoiceLineItemId() > 0 )
                {
                    lineItem.setSelected( true );    
                }
                lineItem.setSalesOrderId        ( rset.getInt( "SALESORDER_ID" ) );
                lineItem.setLineItemId          ( rset.getInt( "LINEITEM_ID" ) );
                lineItem.setLineItemNo          ( rset.getInt( "COOL_LINE_ITEM_NO" ) );
                lineItem.setPoLineItemNo        ( rset.getInt( "COOL_PO_LINE_ITEM_NO" ) );
                lineItem.setProductId           ( rset.getInt( "PRODUCT_ID" ) );
                lineItem.setManufacturingPartNo ( rset.getString( "PART_NUMBER" ) );
                lineItem.setMarketingPartNo     ( rset.getString( "MKT_PART_NUMBER" ) );
                lineItem.setCustomerPartNo      ( rset.getString( "CUSTOMER_PART_NUMBER" ) );
                lineItem.setQuantity            ( rset.getInt( "COOL_QTY" ) );
                lineItem.setTotalInvoicedQty    ( rset.getInt( "TOTAL_INVOICED_QTY" ) );
                lineItem.setQtyAvailable        ( rset.getInt( "QTY_AVAILABLE" ) ); 
                lineItem.setQtyPulled           ( rset.getInt( "PILI_QTY_PULLED" ) ); 
                lineItem.setUnitPriceAsDouble   ( rset.getFloat( "COOL_UNIT_COST" ) );
                lineItem.setAmountAsDouble      ( rset.getFloat( "COOL_AMOUNT" ) );
                lineItem.setRequestDate 	    ( StringUtils.formatDateToString( rset.getDate( "COOL_REQUESTED_DATE" ), null ) );
                lineItem.setBookingDate 	    ( StringUtils.formatDateToString( rset.getDate( "COOL_BOOKING_DATE" ), null ) );
                lineItem.setScheduleDate 	    ( StringUtils.formatDateToString( rset.getDate( "COOL_SCHEDULE_DATE" ), null ) );
                lineItem.setRevisedDate 	    ( StringUtils.formatDateToString( rset.getDate( "COOL_REVISED_DATE" ), null ) );
                lineItem.setCancel              ( rset.getBoolean( "COOL_IS_CANCEL" ) );
                lineItem.setStatus		        ( rset.getString( "COOL_STATUS" ) );
                lineItem.setTaxable		        ( rset.getBoolean( "COOL_TAXABLE" ) );
                lineItem.setDescription         ( rset.getString( "COOL_DESCRIPTION" ) );

                lineItemList.add( lineItem );
            }
        }
        catch (Exception e) {
            throw new ScalarException ("Could not retrieve Proforma Sales Order LineItem list from database", e);
        }
        finally {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }
        return lineItemList;
    }

    public static void saveProformaInvoiceInfo ( DBTransaction dbTransaction , ProformaInvoiceForm invoiceForm, User user ) throws ScalarException
    {
        Connection conn         = null;
		CallableStatement cstmt = null;
        boolean success         = false;
        Address bankAddress     = invoiceForm.getBankAddress();

        try
        {
            if( dbTransaction == null )
			{
				conn = DataSource.getConnection();
			}
			else
			{
				conn = dbTransaction.getConnection();
			}

            cstmt = conn.prepareCall ( "{ CALL SP_CREATE_UPDATE_PROFORMA_INVOICE_DETAILS( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );

            cstmt.setInt    ( 1, invoiceForm.getInvoiceId() );
            cstmt.setString ( 2, invoiceForm.getInvoiceNo() );
            cstmt.setString ( 3, invoiceForm.getInvoiceDate() );
            cstmt.setInt    ( 4, invoiceForm.getSalesOrderId() );
            cstmt.setString ( 5, user.getUserName() );
            cstmt.setString ( 6, invoiceForm.getBankName() );
            cstmt.setString ( 7, invoiceForm.getBankAddress1() );
            cstmt.setString ( 8, bankAddress.getAddress2() );
            cstmt.setString ( 9, bankAddress.getAddress3() );
            cstmt.setString ( 10, bankAddress.getAddress4() );
            cstmt.setString ( 11, bankAddress.getCity() );
            cstmt.setString ( 12, bankAddress.getState() );
            cstmt.setString ( 13, bankAddress.getZipCode() );
            cstmt.setString ( 14, bankAddress.getCountry() );
            cstmt.setString ( 15, invoiceForm.getAba() );
            cstmt.setString ( 16, invoiceForm.getAcct() );
            cstmt.setString ( 17, invoiceForm.getSwiftcode() );
            cstmt.setInt    ( 18, invoiceForm.getBankId() );
            cstmt.registerOutParameter( 19, Types.INTEGER );

			cstmt.executeUpdate();

            invoiceForm.setInvoiceId ( cstmt.getInt( 19 ) );
            success = true;
        }
        catch( Exception e )
        {
            e.printStackTrace();
			throw new ScalarException ( "Could not Save the Proforma Invoice Details into database.");
        }
        finally
		{
			if ( dbTransaction == null )
			{
				try
				{
					if (conn != null)
					{
                        if( success == true )
                        {
						    conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
					}
				}
                catch (SQLException ignore) {System.out.println("Sql exception ");}
			}
			if ( cstmt != null )
			{
                try { cstmt.close(); } catch (SQLException ignore){}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch (SQLException ignore){}
            }
	    }

    }

    public static void saveProformaInvoiceLineItemDetails( DBTransaction dbTransaction, ProformaInvoiceForm invoiceForm ) throws ScalarException
    {

        Connection conn 		        = null;
		CallableStatement cstmt         = null;
        boolean success                 = false;
        Iterator pulledLotListIterator  = invoiceForm.getLineItemList().iterator();
        ProformaInvoiceLineItem lineItem= null;

		try
		{
            if( dbTransaction == null )
            {
			    conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            cstmt = conn.prepareCall( "{ CALL SP_CREATE_UPDATE_PROFORM_INVOICE_LINE_ITEM_DETAILS( ?, ?, ?, ?, ?, ? ) }" );

            while( pulledLotListIterator.hasNext() )
            {
                lineItem = ( ProformaInvoiceLineItem ) pulledLotListIterator.next();

                cstmt.setInt    ( 1, lineItem.getProInvoiceLineItemId() );
                cstmt.setInt    ( 2, invoiceForm.getInvoiceId() );
                cstmt.setInt    ( 3, lineItem.getLineItemId() );
                cstmt.setInt    ( 4, lineItem.getQtyAvailable() );
                if( lineItem.isSelected() == false )
                {
                    lineItem.setQtyPulled( 0 );
                }
                cstmt.setInt    ( 5, lineItem.getQtyPulled() );

                cstmt.registerOutParameter( 6, Types.INTEGER );
                cstmt.executeUpdate();

                lineItem.setProInvoiceLineItemId ( cstmt.getInt( 6 ) );
            }
            success = true;
		}
		catch( Exception e )
		{
            e.printStackTrace();
            Logger.error( "Error while saving the Proform Invoice Line Items details", e );
			throw new ScalarException ("Error while saving the Proform Invoice Line Items details");
		}
		finally
		{
            if( dbTransaction == null )
            {
                if( success == true )
                {
                    try
                    {
                        conn.commit();
                    }
                    catch( Exception e )
                    {
                        throw new ScalarException( "Errow while commiting the Proform Invoice Line Items details save" );
                    }
                }
                else
                {
                    try
                    {
                        conn.rollback();
                    }
                    catch( Exception e )
                    {
                        throw new ScalarException( "Errow while rolling back the Proform Invoice Line Items details save" );
                    }
                }
            }

			if (cstmt != null)
			{
				try { cstmt.close(); } catch (SQLException ignore) {}
            }

			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
    }

    public static ArrayList loadProformaInvoiceList( DBTransaction dbTransaction, ProformaInvoiceForm invoiceForm ) throws ScalarException
    {
        ArrayList invoiceList               = new ArrayList();
        ProformaInvoiceLineItem lineItem    = null;
		Connection conn 		            = null;
		PreparedStatement pstmt             = null;
		ResultSet rs 			            = null;

		try
		{
            if( dbTransaction == null )
            {
                conn	= DataSource.getConnection();
            }
            else
            {
                conn	= dbTransaction.getConnection();
            }

            pstmt 	= conn.prepareStatement( "SELECT DISTINCT PI.PROFORMA_INVOICE_ID, PROFORMA_INVOICE_NO, CONVERT( VARCHAR, PI_DATE, 101 ) AS PI_DATE, PI.SALESORDER_ID, SO.SALESORDER_NO, C.C_NAME, PI.STATUS FROM PROFORMA_INVOICE PI, PROFORMA_INVOICE_LINE_ITEM PILI, SALES_ORDER SO, CUSTOMER_OPEN_ORDER_LINEITEM COOL, PRODUCT P, CUSTOMER C WHERE PI.PROFORMA_INVOICE_ID = PILI.PROFORMA_INVOICE_ID AND SO.SALESORDER_ID = PI.SALESORDER_ID AND PILI.LINEITEM_ID = COOL.LINEITEM_ID AND COOL.PRODUCT_ID = P.PRODUCT_ID AND SO.CUSTOMER_ID = C.CUSTOMER_ID  " + getQueryStringForProformaInvoiceSearch( invoiceForm ) + " ORDER BY PI.PI_DATE DESC, PI.PROFORMA_INVOICE_ID DESC" );

            rs 		= pstmt.executeQuery();

			while ( rs.next() )
			{

                lineItem = new ProformaInvoiceLineItem();

                lineItem.setInvoiceId           ( rs.getInt( "PROFORMA_INVOICE_ID" ) );
                lineItem.setInvoiceNo           ( rs.getString( "PROFORMA_INVOICE_NO" ) );
                lineItem.setInvoiceDate         ( rs.getString( "PI_DATE" ) );
                lineItem.setSalesOrderId        ( rs.getInt( "SALESORDER_ID" ) );
                lineItem.setSalesOrderNo        ( rs.getString( "SALESORDER_NO" ) );
                lineItem.setCustomerName        ( rs.getString( "C_NAME" ) );
                lineItem.setStatus              ( rs.getString( "STATUS" ) );

                invoiceList.add( lineItem );
			}
		}
		catch( Exception e )
		{
            e.printStackTrace();
            Logger.error( "Error while retreiving the Proforma Invoice list", e );
			throw new ScalarException ("Error while retreiving the Proforma Invoice list");
		}
		finally
		{
			if (rs != null)
			{
				try { rs.close(); } catch (SQLException ignore) {}
            }

			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
            }

			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
        return invoiceList;
    }

    private static String getQueryStringForProformaInvoiceSearch( ProformaInvoiceForm invoiceForm )
    {
        StringBuffer query = new StringBuffer();
        if( invoiceForm != null )
        {
            if( invoiceForm.getSearchSalesOrderId() != -1 )
            {
                query.append( " AND PI.SALESORDER_ID = " + invoiceForm.getSearchSalesOrderId() );
            }
            if( invoiceForm.getSearchCustomerId() != -1 )
            {
                query.append( " AND C.CUSTOMER_ID = " + invoiceForm.getSearchCustomerId() );
            }
            if( invoiceForm.getSearchProcessstepId() != -1 )
            {
                query.append( " AND P.PROCESSSTEP_ID = " + invoiceForm.getSearchProcessstepId() );
            }
            if( invoiceForm.getSearchInvoiceNo().trim().length() > 0 )
            {
                query.append( " AND PI.PROFORMA_INVOICE_NO = " + invoiceForm.getSearchInvoiceNo() );
            }
            if( ("-1".equalsIgnoreCase( invoiceForm.getSearchYear()  ) )  == false )
            {
                query.append( " AND DATEPART( yyyy, PI.PI_DATE ) = " + invoiceForm.getSearchYear() );
            }
        }
        return query.toString();
    }

    public static void loadCompleteProformaInvoiceDetails( ProformaInvoiceForm invoiceForm, int invoiceId ) throws ScalarException
    {
        try
		{
            int listSize                                = 0;
            int runningTotal                            = 0;

            ClientInfo clientInfo = UserDAO.getClientAddressInfo();

            invoiceForm.setInvoiceId( invoiceId );

            loadProformaInvoiceDetails( null, invoiceForm );

            invoiceForm.setLineItemList( ProformaInvoiceDAO.loadLineItemDetails( null, invoiceForm.getSalesOrderId(), invoiceId ) );

           /* pulledReleaseLotsList   = releaseForm.getPulledLotsList();
            listSize                = pulledReleaseLotsList.size();
            for( int j = 0; j < listSize; j++ )
            {
                pulledReleaseLot = ( ReleaseLot ) pulledReleaseLotsList.get( j );
                runningTotal = runningTotal + pulledReleaseLot.getQtyPulled();
            }
            releaseForm.setTotalPulledQty( runningTotal );*/

        }
		catch( Exception e )
		{
            e.printStackTrace();
			throw new ScalarException ("Error while retreiving the complete Proforma Invoice details");
		}
    }

    public static void loadProformaInvoiceDetails( DBTransaction dbTransaction, ProformaInvoiceForm invoiceForm ) throws ScalarException
    {
		Connection conn 		    = null;
		PreparedStatement pstmt     = null;
		ResultSet rs 			    = null;
        SalesOrder salesOrder       = null;
        Address address             = new Address();
        try
		{
            if( dbTransaction == null )
            {
                conn	= DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT DISTINCT PROFORMA_INVOICE_NO, CONVERT( VARCHAR, PI_DATE, 101 ) AS PI_DATE, SALESORDER_ID, PI_BANK_NAME, PI_ABA, PI_ACCT, PI_SWIFT_CODE, ADDRESS_ID, STATUS, BANKID FROM PROFORMA_INVOICE WHERE PROFORMA_INVOICE_ID = ?" );
            pstmt.setInt( 1, invoiceForm.getInvoiceId() );
			rs = pstmt.executeQuery();

			if ( rs.next() )
			{
                invoiceForm.setInvoiceNo    ( rs.getString("PROFORMA_INVOICE_NO") );
                invoiceForm.setInvoiceDate  ( rs.getString("PI_DATE") );
                invoiceForm.setSalesOrderId ( rs.getInt( "SALESORDER_ID" ) );
                invoiceForm.setBankName     ( rs.getString("PI_BANK_NAME") );
                invoiceForm.setAba          ( rs.getString("PI_ABA") );
                invoiceForm.setAcct         ( rs.getString("PI_ACCT") );
                invoiceForm.setSwiftcode    ( rs.getString("PI_SWIFT_CODE") );
                address.setAddressId        ( rs.getInt( "ADDRESS_ID" ) );
                invoiceForm.setBankAddress  ( address );
                invoiceForm.setStatus       ( rs.getString("STATUS") );
                invoiceForm.setBankId       ( rs.getInt("BANKID") );
            }

            salesOrder = SalesOrderDAO.loadSalesOrder( invoiceForm.getSalesOrderId() + "" );
            invoiceForm.setSalesOrder( salesOrder );
            invoiceForm.setSalesOrderId( salesOrder.getSalesOrderId() );

            invoiceForm.setBankAddress( CustomerDAO.getAddress( invoiceForm.getBankAddress().getAddressId() ) );

        }
		catch( Exception e )
		{
            e.printStackTrace();
			throw new ScalarException ("Error while retreiving the Proforma Invoice details");
		}
		finally
		{
			if (rs != null)
			{
				try { rs.close(); } catch (SQLException ignore) {}
            }

			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
            }

			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
    }

    public static ArrayList loadApprovedSalesOrderCustomerPOList( DBTransaction dbTransaction ) throws ScalarException
    {

        Connection conn             = null;
        PreparedStatement pstmt     = null;
        ResultSet rset              = null;
        ArrayList salesOrderList    = new ArrayList();
        String soCustomerPONo       = null;

        try
        {
            if( dbTransaction == null )
            {
                conn	= DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "SELECT DISTINCT SO.SO_CUSTOMER_PO_NUMBER FROM SALES_ORDER SO, CUSTOMER_OPEN_ORDER_LINEITEM COOL WHERE COOL.SALESORDER_ID = SO.SALESORDER_ID AND ( SO_IS_SAMPLE = 0 OR SO_IS_SAMPLE = 1 ) AND COOL_STATUS = 'APPROVED' AND SO.SALESORDER_ID NOT IN ( SELECT DISTINCT SALESORDER_ID FROM CUSTOMER_SHIPPING_TABLE  WHERE ( CST_STATUS = 'ISSUED' OR CST_STATUS = 'SHIPPED' ) )" );
            rset = pstmt.executeQuery();

            while( rset.next() )
            {
                soCustomerPONo = rset.getString( "SO_CUSTOMER_PO_NUMBER" );
                salesOrderList.add( new LabelValueBean ( soCustomerPONo, soCustomerPONo ) );
            }
        }
        catch (Exception e)
        {
            throw new ScalarException ("Could not retrieve Sales Order Customer POs from database", e);
        }
        finally
        {
            if (rset != null) {
                try { rset.close(); } catch (SQLException ignore) {}
            }
            if (pstmt != null) {
                try { pstmt.close(); } catch (SQLException ignore) {}
            }
            if (conn != null) {
                try { conn.close(); } catch (SQLException ignore) {}
            }
        }

        return salesOrderList;
    }

    public static boolean isInvoiceNumberExists ( DBTransaction dbTransaction, String invoiceNumber ) throws ScalarException
    {
        Connection conn         = null;
        PreparedStatement pstmt = null;
        ResultSet rset          = null;
        boolean success         = false;
        boolean isExists        = false;

        try
        {
            if ( dbTransaction != null )
            {
                conn = dbTransaction.getConnection();
            }
            else
            {
                conn = DataSource.getConnection();
            }

            pstmt = conn.prepareStatement ( "SELECT COUNT(*) AS INVOICE_NO_COUNT FROM PROFORMA_INVOICE WHERE PROFORMA_INVOICE_NO = ?" );

            pstmt.setString ( 1, invoiceNumber );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                int count = rset.getInt ( "INVOICE_NO_COUNT" );
                if ( count > 0 )
                {
                    isExists = true;
                }
            }
            success = true;
        }
        catch ( Exception  e )
        {
            e.printStackTrace();
            throw new ScalarException ( "Invoice Number already exists" );
        }
        finally
        {
             if ( dbTransaction == null )
            {
                if ( conn != null )
                {
                    try
                    {
                        if ( success )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }catch ( Exception e ) { }
                }
            }
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ){ }
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ){ }
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ){ }
            }
        }
        return isExists;
    }


    public static void updateProformaInvoiceStatus( DBTransaction dbTransaction, int invoiceId, String status, String userName ) throws ScalarException
    {
        Connection conn = null;
		PreparedStatement pstmt = null;
        boolean success = false;

        try
		{
            if( dbTransaction == null )
            {
			    conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            pstmt = conn.prepareStatement( "UPDATE PROFORMA_INVOICE SET STATUS = ?, PI_MODIFIED_BY = ?,  PI_MODIFIED_ON = CURRENT_TIMESTAMP WHERE PROFORMA_INVOICE_ID = ?" );

            if( pstmt != null )
            {
                pstmt.setString ( 1, status );
                pstmt.setString ( 2, userName );
                pstmt.setInt    ( 3, invoiceId );
                pstmt.executeUpdate();

                success = true;
            }
        }
		catch( Exception e )
		{
            e.printStackTrace();
            Logger.error( "Error while updating the proforma invoice status", e );
			throw new ScalarException ("Error while updating the proforma invoice status");
		}
		finally
		{
            if( dbTransaction == null )
            {
                if( success == true )
                {
                    try
                    {
                        conn.commit();
                    }
                    catch( Exception e )
                    {
                        throw new ScalarException( "Errow while commiting the proforma invoice details save" );
                    }
                }
                else
                {
                    try
                    {
                        conn.rollback();
                    }
                    catch( Exception e )
                    {
                        throw new ScalarException( "Errow while rolling back the proforma invoice details save" );
                    }
                }
            }

			if (pstmt != null)
			{
				try { pstmt.close(); } catch (SQLException ignore) {}
            }

			if (conn != null)
			{
				try { conn.close(); } catch (SQLException ignore) {}
			}
		}
    }
    public static ArrayList loadBankersListByCustomer(DBTransaction dbTransaction, int customerId ) throws ScalarException
        {
            Connection conn             = null;
            PreparedStatement pstmt     = null;
            ResultSet rset              = null;
            ArrayList salesOrderlist    = new ArrayList();

            try
            {
                if ( dbTransaction == null )
                {
                    conn = DataSource.getConnection();
                }
                else
                {
                    conn = dbTransaction.getConnection();
                }

                pstmt = conn.prepareStatement( "SELECT  BANKID, BANKNAME FROM CLIENT_BANKINFO" );
                rset = pstmt.executeQuery();

                while( rset.next() )
                {
                    salesOrderlist.add( new LabelValueBean( rset.getString ( "BANKNAME" ), rset.getString ( "BANKID" ) ) );
                }
            }
            catch( Exception e )
            {
                throw new ScalarException ( "Could not retrieve approved sales order numbers from database", e );
            }
            finally
            {
                if (rset != null) {
                    try { rset.close(); } catch (SQLException ignore) {}
                }
                if (pstmt != null) {
                    try { pstmt.close(); } catch (SQLException ignore) {}
                }
                if (conn != null) {
                    try { conn.close(); } catch (SQLException ignore) {}
                }
            }

            return salesOrderlist;
        }

     private static String loadSearchString(String salesOrderId, String customerId,String manufacturingPartNo , int isSample,String poNo)
                             throws ScalarException
    {
        String search_string = "";


            search_string = " So_Is_Sample = So_Is_Sample";

        if((salesOrderId != null) && (salesOrderId.trim().length() != 0) && (!"-1".equals(salesOrderId)))
            search_string = search_string + " and Sales_Order.SalesOrder_Id like '"+ StringUtils.formatToHandleQuote(salesOrderId.trim()) +"%'";

        if((manufacturingPartNo != null) && (manufacturingPartNo.trim().length() > 0))
             search_string = search_string + " and Product_Id = " + MarketingDAO.loadProductIdForSearch(manufacturingPartNo);

        if((customerId != null) && (!"0".equals(customerId))  && (!"-1".equals(customerId)))
            search_string = search_string +  " and Customer_ID = " + Integer.parseInt(customerId);

        if((poNo != null) && (!"-1".equals(poNo)))
        {
            search_string = search_string + " and Sales_Order.SO_CUSTOMER_PO_NUMBER = '" + poNo + "'";
        }

         return search_string + " ORDER BY SalesOrder_No";

    }

     public static ArrayList loadSalesOrdersWithAll(String salesOrderId,String customerId,String manufacturingPartNo, int isSample,String poNo) throws ScalarException
    {
            if (salesOrderId == null)
            {
                return null;
            }

            Connection conn = null;
            PreparedStatement pstmt = null;
            ArrayList ordersList = new ArrayList();
            ResultSet rset = null;
            String searchString = null;

            try {

                searchString = loadSearchString(salesOrderId, customerId, manufacturingPartNo, isSample, poNo);
                conn = DataSource.getConnection();
                pstmt = conn.prepareStatement ("SELECT DISTINCT Sales_Order.SalesOrder_ID,SO_Customer_PO_Number,convert(varchar,SO_Booking_Date,101) SO_Booking_Date,Customer_ID,SalesOrder_No FROM Customer_Open_Order_LineItem, Sales_Order WHERE  Sales_Order.SalesOrder_Id=Customer_Open_Order_LineItem.SalesOrder_ID and " + searchString );
                rset = pstmt.executeQuery();
                Orders orders = null;

                while (rset.next()) {
                    orders = new Orders();
                    orders.setSalesOrderId(rset.getInt("SalesOrder_ID"));
                    orders.setCustomerPO(rset.getString("SO_Customer_PO_Number"));
                    orders.setBookingDate(rset.getString("SO_Booking_Date"));
                    orders.setCustomerName(CustomerDAO.loadCustomerName(rset.getInt("Customer_ID")));
                    orders.setSalesOrderNo(rset.getString("SalesOrder_No"));
                    ordersList.add(orders);

                }
            }
            catch (Exception sqle) {
                sqle.printStackTrace();
                throw new ScalarException ("Could not salesOders for salesOrderId from database", sqle);
            }
            finally {
                if (rset != null) {
                    try { rset.close(); } catch (SQLException ignore) {}
                }
                if (pstmt != null) {
                    try { pstmt.close(); } catch (SQLException ignore) {}
                }
                if (conn != null) {
                    try { conn.close(); } catch (SQLException ignore) {}
                }
            }

            return ordersList;
     }

      public static ArrayList loadManufacturingProducts() throws Exception
    {
        ArrayList parts = new ArrayList ();
        Iterator list   = null;
            list = FinishedGoodsDAO.loadAllFinishedGoods().iterator ();
        parts.add ( new org.apache.struts.util.LabelValueBean ( "All Products", "" ) );
        while ( list.hasNext () )
        {
            FinishedGoods product = ( FinishedGoods ) list.next ();
            //It is better to replace with product Id here, instead of again searching the database with marketingpart no for productid
            //But i am leaving the other code as it is because we need to change lot of the code in the salesDAO. ;)
            parts.add ( new org.apache.struts.util.LabelValueBean ( product.getManufacturingPartNo (), product.getManufacturingPartNo () ) );
        }

        return parts;
    }
    /**
     * This method will return the auto generated Proforma Invoice number to be used to create new Proforma Invoice. <br>
     * Auto Generated Invoice Number will be in the format: <b>&lt;Proforma PREFIX&gt;-&lt;yMMdd&gt;&lt;seq&gt; </b><br>
     * where <b>&lt;Proforma PREFIX&gt;</b> - Proforma Invoice Prefix which is set in the company setup. if it is empty then hyphen(-) is not added to the auto Proforma Invoice number.<br>
     * <b>&lt;yMMdd&gt;</b> - single digit year(y) Two digit month(MM) Two digit day(dd). month range will be 01-12 and day will be 01-31<br>
     * <b>&lt;seq&gt;</b> - sequence number. range will be 00 - 99<br>
     * For Ex: PI-7011700 --> PI - Prefix <br>
     *                    --> 7 - year(2007)<br>
     *                    --> 01 - month(January)<br>
     *                    --> 17 - day<br>
     *                    --> 00 - sequence</br>
     * when PI prefix is not given then the auto Proforma Invoice number will be: <b>7011700</b>
     * @return returns the auto generated Proforma Invoice number.
     */
    public static String generateAutomatedProformaInvoiceNo() throws ScalarException
    {
        String newInvoiceNumber = null;
        String sequence = "00";
        String invoiceNumber = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rset = null;

        String salesOrderPrefix = null;
        int salesOrderLength = 0;
        Calendar cl	= Calendar.getInstance();
        newInvoiceNumber = StringUtils.formatDateToString(cl.getTime(), "yyMMdd" );
        newInvoiceNumber = newInvoiceNumber.substring(1);

        try
        {
            conn = DataSource.getConnection();
            /*pstmt = conn.prepareStatement( SqlQueryMappings.get( "sales.salesOrder.getSalesOrderInfo" ) );
            rset = pstmt.executeQuery();
            if( rset.next())
            {
                salesOrderPrefix = rset.getString("Sales_Order_Prefix");
                salesOrderLength = rset.getInt("Sales_Order_Length");
            }

            try { rset.close(); rset = null; } catch ( SQLException ignore ) {}
            try { pstmt.close(); pstmt = null; } catch ( SQLException ignore ) {}*/

            pstmt = conn.prepareStatement( "SELECT  TOP 1 PROFORMA_INVOICE_NO FROM PROFORMA_INVOICE WHERE LEN(SUBSTRING(PROFORMA_INVOICE_NO,CHARINDEX('-',PROFORMA_INVOICE_NO)+1,LEN(PROFORMA_INVOICE_NO))) = 7 ORDER BY PROFORMA_INVOICE_ID DESC" );
            rset = pstmt.executeQuery();
            if ( rset.next() )
            {
                invoiceNumber = rset.getString ( "PROFORMA_INVOICE_NO" );
            }
        }
        catch ( Exception e )
        {
            throw new ScalarException ( "Could not get Automated Proforma Invoice No from database", e );
        }
        finally
        {
            if ( rset != null )
            {
                try { rset.close(); } catch ( SQLException ignore ) {}
            }
            if ( pstmt != null )
            {
                try { pstmt.close(); } catch ( SQLException ignore ) {}
            }
            if ( conn != null )
            {
                try { conn.close(); } catch ( SQLException ignore ) {}
            }
        }
        if ( invoiceNumber != null )
        {
            int seq = 0;
            try
            {
                seq = Integer.parseInt ( invoiceNumber.substring( invoiceNumber.length()-2 ) );
            } catch ( NumberFormatException e ) { seq = -1; }
            seq = seq + 1;
            if ( seq > 99 )
            {
                seq = 0;
            }
            sequence = (seq<10) ? "0"+seq : Integer.toString(seq);
        }
        newInvoiceNumber = newInvoiceNumber + sequence;
        /*if( salesOrderPrefix != null && salesOrderPrefix.trim().length() != 0 )
        {
            newSalesOrderNumber = salesOrderPrefix + "-" + newSalesOrderNumber;
        }*/
        return newInvoiceNumber;
    }



}
